package com.servlet;
/**
 * 题库操作servlet
 */

import com.bean.BhUser;
import com.bean.Bhtopic;
import com.bean.Topic;
import com.dao.BhSyCaseDao;
import com.dao.TopicDao;
import com.dao.impl.BhSyCaseDaoImpl;
import com.dao.impl.TopicDaoImpl;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.springframework.web.multipart.*;

import javax.servlet.annotation.*;
import javax.servlet.http.*;
import java.io.*;
import java.net.*;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@WebServlet("/Excel/*")
public class ExcelServlet extends BaseServlet {
    public static XSSFSheet sheet;
    BhSyCaseDao bcd = new BhSyCaseDaoImpl();
    //-------------------老师通过Excel上传试卷题目----------------------
    public void upLoadExcel(HttpServletRequest request, HttpServletResponse response){
        HttpSession session = request.getSession();
        BhUser bu = (BhUser) session.getAttribute("BhSyUser");
        DiskFileItemFactory dff = new DiskFileItemFactory();//配置上传参数
        ServletFileUpload sfu = new ServletFileUpload(dff);
        sfu.setHeaderEncoding("UTF-8");//中文处理
        //项目的发布位置
        String apppath = request.getServletContext().getRealPath("/file");
        System.out.printf("上传位置="+apppath);
        String isSameTop = null;
        try {
            List<FileItem> list = sfu.parseRequest(request);
            //存盘 d:/xxxx/xxx/file
            for (FileItem fi:list) {
                if (fi.isFormField()){
                    continue;
                }else {
                    //文件
                    File file = new File(apppath+"/"+fi.getName());
                    fi.write(file);
                    //获取当前题库的名字
                    String topicKuName = (String) session.getAttribute("TopicKuName");

                    getExcelMes(topicKuName ,apppath+"/"+fi.getName(),bu.getuID());
                }
            }
            session.setAttribute("flagUlCase",1);
            request.getRequestDispatcher("/WEB-INF/jsp/AddTopicExcelYes.jsp").forward(request,response);
        } catch (FileUploadException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    //----将上传的题目下载到本地，执行TopicDaoImpl的添加SQL语句---------------------------
    public void getExcelMes(String topicKuName, String appPath,int uid){
        System.out.println("[getExcelMes方法中接收到题库---------------------------------------------------]"+topicKuName);
        boolean flag = false;
        List<Bhtopic> listBh = new ArrayList<>();
        Bhtopic bt = null;
        File f = new File(appPath);
        try {
            InputStream input = new FileInputStream(f);
            HSSFWorkbook sheets = new HSSFWorkbook(input);
            //XSSFSheet sheet = sheets.getSheet("Sheet1");  // 根据标签去第几个表格
            HSSFSheet sheet = sheets.getSheetAt(0);
            //获取行数
            int rows = sheet.getPhysicalNumberOfRows();
            HSSFRow row = sheet.getRow(0);
            int cols = row.getPhysicalNumberOfCells();
            System.out.println("文件多少行"+rows);
            System.out.println("文件多少列"+cols);
            //Excel表格题目集合
            List<Topic> list = new ArrayList<Topic>();
            for (int i = 1; i < rows ; i++) {
                Row r = sheet.getRow(i);
                if(
                    r.getCell(0)==null ||
                    r.getCell(1)==null ||
                    r.getCell(2)==null ||
                    r.getCell(3)==null ||
                    r.getCell(4)==null ||
                    r.getCell(5)==null ||
                    r.getCell(6)==null ||
                    r.getCell(7)==null
                ){
                    continue;
                }

                list.add(new Topic(
                        uid,
                        r.getCell(0).toString(),
                        r.getCell(1).toString(),
                        r.getCell(2).toString(),
                        r.getCell(3).toString(),
                        r.getCell(4).toString(),
                        r.getCell(5).toString(),
                        r.getCell(6).toString(),
                        Integer.valueOf(r.getCell(7).toString())


                ));



                System.out.println("[list]"+list.toString());
            }
            TopicDao topicDao = new TopicDaoImpl();
            topicDao.insertTopic(topicKuName, list);


        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void cccccc() throws SQLException {
        List list = new ArrayList();
        list.add(new Topic());
        bcd.bhInertExcelTopic(list, 2);


    }


    /**
     * 添加题库到数据库
     * @param request
     * @param response
     */
    public void downLoad(HttpServletRequest request, HttpServletResponse response) {

        //通过文件名找出文件的所在目录
        String URL = request.getRealPath("file/Test1.xls");

        //得到要下载的文件
        File file = new File(URL);

        //如果文件不存在
        if(!file.exists()){
            //如果文件不存在，进行处理
            System.out.println("文件不存在");
            int  i=1/0;//系统会报错，除数不能为0.
            // return "modules/cms/front/themes/"+site.getTheme()+"/frontError";
        }
        System.out.println("文件存在");

        InputStream inputStream = null;
        OutputStream outputStream = null;
        //创建缓冲区
        byte[] b= new byte[1024];
        int len = 0;
        try {
            //读取要下载的文件，保存到文件输入流
            inputStream = new FileInputStream(file);
            outputStream = response.getOutputStream();
            response.setContentType("application/force-download");
            String filename = file.getName();
            //设置响应头，控制浏览器下载该文件
            response.addHeader("Content-Disposition","attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
            response.setContentLength( (int) file.length( ) );
            //循环将输入流中的内容读取到缓冲区当中
            while((len = inputStream.read(b)) != -1){
                //输出缓冲区的内容到浏览器，实现文件下载
                outputStream.write(b, 0, len);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(inputStream != null){
                try {
                    inputStream.close();
                    inputStream = null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(outputStream != null){
                try {
                    outputStream.close();
                    outputStream = null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }


}
